package net.hn.hnms.biz.safety.fiber.service;

import net.hn.hnms.biz.api.safety.monitor.model.ChartVo;
import net.hn.hnms.biz.safety.fiber.domain.AlarmDataVo;
import net.hn.hnms.biz.safety.fiber.domain.Channel;
import net.hn.hnms.biz.safety.fiber.domain.FiberDevice;
import net.hn.hnms.sys.common.core.domain.ResponsePage;
import net.hn.hnms.sys.common.core.utils.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Author: PanSX
 * Date: 2023/4/18 16:07
 */
@Service
public class FiberService {
    @Autowired
    @Qualifier("fiberJdbcTemplate")
    private JdbcTemplate fiberJdbcTemplate;

    /**
     * 级联下拉框
     *
     * @param mineCode
     * @return
     */
    public List<FiberDevice> queryDevice(String mineCode) {
        if (StringUtils.isNotBlank(mineCode)) {
            mineCode = " and mine_code='" + mineCode + "'";
        } else {
            mineCode = "";
        }
        String devSql = "SELECT device_id as deviceId,note as name FROM dts_device where 1=1 %s";
        List<FiberDevice> devices = fiberJdbcTemplate.query(String.format(devSql, mineCode), new BeanPropertyRowMapper<>(FiberDevice.class));
        for (FiberDevice device : devices) {
            String channelSql = "SELECT channel_id,name FROM dts_channels where device_id='%s' and is_used=1 %s";
            List<Channel> channels = fiberJdbcTemplate.query(String.format(channelSql, device.getDeviceId(), mineCode), new BeanPropertyRowMapper<>(Channel.class));
            device.setChannels(channels);
        }
        return devices;
    }

    /**
     * 实时数据
     *
     * @param deviceId
     * @param channelId
     * @param mineCode
     * @return
     */
    public String realData(String deviceId, String channelId, String mineCode) {
        if (StringUtils.isNotBlank(mineCode)) {
            mineCode = " and mine_code='" + mineCode + "'";
        } else {
            mineCode = "";
        }
        String sql = "SELECT temperature_data FROM \"dts_raw_data_realtime\" where device_id='%s' and channel_id=%s %s";
        try {
            return fiberJdbcTemplate.queryForObject(String.format(sql, deviceId, channelId, mineCode), String.class);
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    public ResponsePage<AlarmDataVo> getAlarmTable(String deviceId, String channelId, String mineCode, int page) {
        if (StringUtils.isNotBlank(mineCode)) {
            mineCode = " and mine_code='" + mineCode + "'";
        } else {
            mineCode = "";
        }
        String countSql = "SELECT count(*) FROM dts_alarm_data where device_id='%s' and channel_id=%s %s ";
        Integer integer = fiberJdbcTemplate.queryForObject(String.format(countSql, deviceId, channelId, mineCode), Integer.class);
        if (integer > 0) {
            String sql = "SELECT * FROM dts_alarm_data where device_id='%s' and channel_id=%s %s ORDER BY start_time desc limit 10 offset %s";
            List<AlarmDataVo> query = fiberJdbcTemplate.query(String.format(sql, deviceId, channelId, mineCode, page * 10), new BeanPropertyRowMapper<>(AlarmDataVo.class));
            return new ResponsePage<>(query, integer);
        }
        return new ResponsePage<>(new ArrayList(), integer);
    }

    public ChartVo getTemperatureTrend(String deviceId, String channelId, String mineCode) {
        if (StringUtils.isNotBlank(mineCode)) {
            mineCode = " and mine_code='" + mineCode + "'";
        } else {
            mineCode = "";
        }
        String sql = "SELECT\n" +
                "	sj,\n" +
                "	MAX ( wd ) as wd \n" +
                "FROM\n" +
                "	(\n" +
                "	SELECT\n" +
                "		to_char( record_time, 'YYYY-MM-DD' ) AS sj,\n" +
                "		( SELECT MAX ( to_number( T, '9999.99' ) ) FROM UNNEST ( string_to_array( temperature_data, ',' ) ) T ) AS wd \n" +
                "	FROM\n" +
                "		dts_rawdata \n" +
                "	WHERE\n" +
                "		record_time > '%s' \n" +
                "		AND channel_id = '%s' \n" +
                "		AND device_id = '%s' %s \n" +
                "	) tt \n" +
                "GROUP BY\n" +
                "	tt.sj";

        Calendar calendar = Calendar.getInstance();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        List<String> xDate = new ArrayList<>();
        for (int i = 0; i < 7; i++) {
            xDate.add(sdf.format(calendar.getTime()));
            if (i != 6) {
                calendar.set(Calendar.DAY_OF_MONTH, calendar.get(Calendar.DAY_OF_MONTH) - 1);
            }
        }
        Collections.reverse(xDate);
        String[] values = new String[xDate.size()];
        List<Map<String, Object>> maps = fiberJdbcTemplate.queryForList(String.format(sql, xDate.get(0), channelId, deviceId, mineCode));
        for (Map<String, Object> map : maps) {
            values[xDate.indexOf(map.get("sj"))] = map.get("wd") + "";
        }
        ChartVo vo = new ChartVo();
        vo.setXData(xDate);
        vo.setValeus(Arrays.asList(values));
        return vo;
    }
}
